﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Drawing;
using System.Globalization;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using DotNet.Highcharts.Enums;
using DotNet.Highcharts.Helpers;
using DotNet.Highcharts.Options;

namespace WebApplication12
{
    public partial class Default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
            }
        }

        protected void IzbirnikRegije_SelectedIndexChanged(object sender, EventArgs e)
        {
            DropDownList ddl = sender as DropDownList;
            int regijaID = int.Parse(ddl.SelectedValue);

            if (regijaID > 0)
            {
                Regija prebivalstvoRegije = GET_PrebivalstvoRegije(regijaID);
                Regija avtomobilizemRegije = GET_AvtomobilizemRegije(regijaID);

                StatistikaRegija.Text = ddl.SelectedItem.Text;
                StatistikaRegijaStSkupno.Text = prebivalstvoRegije.StSkupno + "";
                StatistikaRegijaStMoskih.Text = prebivalstvoRegije.StMoskih + "";
                StatistikaRegijaStZensk.Text = prebivalstvoRegije.StZensk + "";
                //StatistikaRegijaStAvtomobilov.Text = avtomobilizemRegije.StAvtomobilov + "";

                GET_ObcineInPovrsine(regijaID);

                DotNet.Highcharts.Highcharts chart = new DotNet.Highcharts.Highcharts("chart")
                    .InitChart(new Chart { PlotBackgroundColor = null, PlotBorderWidth = null, PlotShadow = false })
                    .SetTitle(new Title { Text = "Statistika" })
                    .SetTooltip(new Tooltip { PointFormat = "{series.name}: <b>{point.percentage}%</b>", ValueDecimals = 2,  /*, percentageDecimals: 1*/ })
                    .SetPlotOptions(new PlotOptions
                    {
                        Pie = new PlotOptionsPie
                        {
                            Size = new PercentageOrPixel(50, true),
                            ShowInLegend = true,
                            AllowPointSelect = true,
                            Cursor = Cursors.Pointer,
                            DataLabels = new PlotOptionsPieDataLabels
                            {
                                Enabled = false,
                                Color = ColorTranslator.FromHtml("#000000"),
                                ConnectorColor = ColorTranslator.FromHtml("#000000"),
                                Formatter = "function() { return '<b>'+ this.point.name +'</b>: '+ this.percentage +' %'; }"
                            }
                        }
                    }).SetSeries(new Series
                    {
                        Type = ChartTypes.Pie,
                        Name = "Delež",
                        Data = new Data(new object[]
                        {
                            new object[] { "Moški", prebivalstvoRegije.StMoskih },
                            new object[] { "Ženske", prebivalstvoRegije.StZensk }
                        })
                    });
                
                ltrChart.Text = chart.ToHtmlString();

                FieldMinVal.Value = GET_NajmanjsaObcinaRegije(regijaID).ToString();
                FieldMaxVal.Value = GET_NajvecjaObcinaRegije(regijaID).ToString();
            }
            else
            {
                StatistikaRegija.Text = "";
                StatistikaRegijaStSkupno.Text = "";
                StatistikaRegijaStMoskih.Text = "";
                StatistikaRegijaStZensk.Text = "";
                StatistikaRegijaStAvtomobilov.Text = "";
            }
        }

        private Regija GET_PrebivalstvoRegije(int regijaID)
        {
            Regija r = null;

            using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["StatistikaConnectionString"].ConnectionString))
            {
                r = new Regija();

                con.Open();
                using (SqlCommand com = new SqlCommand(
                    "SELECT SUM(population) AS StSkupno, SUM(mens) AS StMoski, SUM(womens) AS StZenske " +
                    "FROM Statistic_population " +
                    "WHERE fk_regionID = @id", con))
                {
                    com.Parameters.AddWithValue("@id", regijaID);

                    using (SqlDataReader sdr = com.ExecuteReader())
                    {
                        if (sdr.Read())
                        {
                            r.StSkupno = int.Parse(sdr["StSkupno"].ToString());
                            r.StMoskih = int.Parse(sdr["StMoski"].ToString());
                            r.StZensk = int.Parse(sdr["StZenske"].ToString());
                        }
                    }
                }

                if (con.State != System.Data.ConnectionState.Closed)
                    con.Close();
            }

            return r;
        }

        private Regija GET_AvtomobilizemRegije(int regijaID)
        {
            Regija r = null;

            using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["StatistikaConnectionString"].ConnectionString))
            {
                r = new Regija();

                con.Open();
                using (SqlCommand com = new SqlCommand("SELECT SUM(cars) AS StAvtov FROM Statistic_cars WHERE fk_regionID = @id", con))
                {
                    com.Parameters.AddWithValue("@id", regijaID);
                    r.StAvtomobilov = int.Parse(com.ExecuteScalar().ToString());
                }
                if (con.State != System.Data.ConnectionState.Closed)
                    con.Close();
            }

            return r;
        }

        private void GET_ObcineInPovrsine(int regijaID)
        {
            bool urejanjePoImenu = HF_Order_Field.Value == "obcina";
            bool urejanjeNarascajoce = HF_Order_Direction.Value == "asc";

            var query = "SELECT Municipality.pk_municipalityID AS obcinaID, Municipality.name, Statistic_population.area " +
                        "FROM Statistic_population " +
                        "INNER JOIN Municipality ON Municipality.pk_municipalityID = Statistic_population.fk_municipalityID " + 
                        "WHERE Statistic_population.fk_regionID = @id " + 
                        "ORDER BY ";
            if (urejanjeNarascajoce)
            {
                if (urejanjePoImenu)
                    query += "Municipality.name ";
                else
                    query += "Statistic_population.area ";
            }
            else
            {
                if (urejanjePoImenu)
                    query += "Municipality.name DESC";
                else
                    query += "Statistic_population.area DESC";
            }

            using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["StatistikaConnectionString"].ConnectionString))
            {
                using (SqlCommand com = new SqlCommand(query, con))
                {
                    com.Parameters.AddWithValue("@id", regijaID);

                    con.Open();
                    using (SqlDataReader sdr = com.ExecuteReader())
                    {
                        SeznamObcin.DataSource = sdr;
                        SeznamObcin.DataBind();
                    }
                }
                if (con.State != System.Data.ConnectionState.Closed)
                    con.Close();
            }
        }

        private string GET_NajmanjsaObcinaRegije(int regijaID)
        {
            string povrsina = "";

            using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["StatistikaConnectionString"].ConnectionString))
            {
                con.Open();
                using (SqlCommand com = new SqlCommand("SELECT MIN(area) AS najmanjsa FROM Statistic_population WHERE fk_regionID = @id", con))
                {
                    com.Parameters.AddWithValue("@id", regijaID);
                    povrsina = com.ExecuteScalar().ToString();
                }
                if (con.State != System.Data.ConnectionState.Closed)
                    con.Close();
            }

            return povrsina.Substring(0, povrsina.Length-1);
        }

        private string GET_NajvecjaObcinaRegije(int regijaID)
        {
            string povrsina = "";

            using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["StatistikaConnectionString"].ConnectionString))
            {
                con.Open();
                using (SqlCommand com = new SqlCommand("SELECT MAX(area) AS najvecj FROM Statistic_population WHERE fk_regionID = @id", con))
                {
                    com.Parameters.AddWithValue("@id", regijaID);
                    povrsina = com.ExecuteScalar().ToString();
                }
                if (con.State != System.Data.ConnectionState.Closed)
                    con.Close();
            }

            return povrsina.Substring(0, povrsina.Length - 1);
        }

        protected void SeznamObcinObcina_Click(object sender, EventArgs e)
        {
            HF_Order_Field.Value = "obcina";
            if (HF_Order_Direction.Value == "asc")
                HF_Order_Direction.Value = "desc";
            else
                HF_Order_Direction.Value = "asc";

            IzbirnikRegije_SelectedIndexChanged(IzbirnikRegije, null);
        }

        protected void SeznamObcinPovrsina_Click(object sender, EventArgs e)
        {
            HF_Order_Field.Value = "povrsina";
            if (HF_Order_Direction.Value == "asc")
                HF_Order_Direction.Value = "desc";
            else
                HF_Order_Direction.Value = "asc";

            IzbirnikRegije_SelectedIndexChanged(IzbirnikRegije, null);
        }
    }

    public class Regija
    {
        public int StSkupno { get; set; }
        public int StMoskih { get; set; }
        public int StZensk { get; set; }
        public int StAvtomobilov { get; set; }
    }
}